CREATE DATABASE AlbumDB
GO

USE [AlbumDB]

CREATE TABLE [User]
(
	ID int NOT NULL IDENTITY(1, 1) CONSTRAINT FK_USER PRIMARY KEY,
	FirstName nvarchar(32) NULL,
	LastName nvarchar(32) NULL,
	Email nvarchar(50) NOT NULL CONSTRAINT UK_EMAIL UNIQUE,
	PasswordHash nvarchar(32) NOT NULL
)

CREATE TABLE Album
(
	ID int NOT NULL IDENTITY(1, 1) CONSTRAINT FK_ALBUM PRIMARY KEY,
	Name nvarchar(50) NOT NULL,
	OwnerUserRef INT NOT NULL CONSTRAINT FK_ALBUM_OWNER_USER_REF REFERENCES [User](ID)
)

CREATE TABLE MObject
(
	ID int NOT NULL IDENTITY(1, 1) CONSTRAINT PK_MObject PRIMARY KEY,
	ParentAlbumRef INT NOT NULL CONSTRAINT FK_MOBJECT_ALBUM_OWNER_REF REFERENCES [Album](ID),
	[Type] tinyint NOT NULL,
	[Data] image NULL
)

CREATE TABLE Tag
(
	ID int NOT NULL IDENTITY(1, 1) CONSTRAINT FK_TAG PRIMARY KEY,
	ParentID int NULL CONSTRAINT FK_TAG_ParentID FOREIGN KEY(ParentID) REFERENCES Tag(ID),
	Name nvarchar(50) NOT NULL,
	
	CONSTRAINT FK_TAG_UNIQUE_NAME UNIQUE(ParentID, Name),
	CONSTRAINT FK_TAG_NOT_PARENT_FOR_ITSELF CHECK (ID <> ParentID)
)

CREATE TABLE MObjectTagRelation
(
	ObjectRef INT NOT NULL 
		CONSTRAINT FK_MOBJECT_TAG_RELATION_OBJECT_REF 
		FOREIGN KEY (ObjectRef) 
		REFERENCES MObject(ID)
		ON DELETE CASCADE,
	TagRef INT NOT NULL 
		CONSTRAINT FK_MOBJECT_TAG_RELATION_TAG 
		FOREIGN KEY (TagRef) 
		REFERENCES Tag(ID)
		ON DELETE CASCADE,
		
	CONSTRAINT PK_MOBJECT_TAG_RELATION PRIMARY KEY (ObjectRef, TagRef)
)

INSERT INTO [User] (FirstName, LastName, Email, PasswordHash)
	VALUES ('Volodymyr', 'Shtenovych', 'ShVolodya@gmail.com', 'test')

INSERT INTO Tag (Name) VALUES ('1')
INSERT INTO Tag (Name, ParentID) VALUES ('1.1', 1)
INSERT INTO Tag (Name) VALUES ('2')
INSERT INTO Tag (Name, ParentID) VALUES ('2.1', 3)
INSERT INTO Tag (Name, ParentID) VALUES ('2.1.1', 4)

